package instructor.sample;

import java.sql.*;

public class Customer {

	private int customerid = 0;
	private String customername = null;
	private String customertypedesc = null;
	private String billingstreet = null;
	private String billingcity = null;
	private String billingstate = null;
	private String billingzip = null;
	private String firstname = null;
	private String lastname = null;
	private String phone = null;
	private String email = null;
	private String position = null;
	private ResultSet rs = null;
	private CallableStatement csmt = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();

	// / USELESS FUNCTIONS THAT ARE GOING AWAY
	public int getCustomerEquipmentNumber() {
		return 0;
	}
	public int getProductName() {
		return 0;
	}
	
	

	public int updateCustomer(String customerid, String customername,
			String billingstreet, String billingcity, String billingstate,
			String billingzip, String contactfirstname, String contactlastname,
			String contactphone, String contactemail, String contactposition)
			throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {
		int ret;
		myDB.createConnection();
		CallableStatement csmt = null;
		String mySQL = "{call CUSTOMERUPDATE(?,?,?,?,?,?,?,?,?,?,?)}";
		csmt = myDB.conn.prepareCall(mySQL);
		csmt.setInt(1, Integer.parseInt(customerid));
		csmt.setString(2, customername);
		csmt.setString(3, billingstreet);
		csmt.setString(4, billingcity);
		csmt.setString(5, billingstate);
		csmt.setString(6, billingzip);
		csmt.setString(7, contactfirstname);
		csmt.setString(8, contactlastname);
		csmt.setString(9, contactphone);
		csmt.setString(10, contactemail);
		csmt.setString(11, contactposition);
		ret = csmt.executeUpdate();
		return ret;
	}

	public void setCustomerInfo(int userid) throws ClassNotFoundException,
			SQLException, InstantiationException, IllegalAccessException {

		myDB.createConnection();
		String myQuery = "Select c.customerid, customername, billingstreet, billingcity, "
				+ "billingstate, billingzip, contactfirstname, contactlastname, "
				+ "contactphone, contactemail, contactposition "
				+ "from customer c, users u "
				+ "where c.customerid = u.customerid and userid = ?";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, userid);
		rs = ps.executeQuery();

		while (rs.next()) {
			customerid = rs.getInt("customerid");
			customername = rs.getString("customername");
			billingstreet = rs.getString("billingstreet");
			billingcity = rs.getString("billingcity");
			billingstate = rs.getString("billingstate");
			billingzip = rs.getString("billingzip");
			firstname = rs.getString("contactfirstname");
			lastname = rs.getString("contactlastname");
			phone = rs.getString("contactphone");
			email = rs.getString("contactemail");
			position = rs.getString("contactposition");
		}
	}

	public ResultSet getOpenRequests(int customerid)
			throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {

		myDB.createConnection();
		String myQuery = "select so.serviceorderid, e.equipmentid, p.productname, "
				+ "st.servicetypedescription, sos.soStatusDescription "
				+ "from serviceorder so, equipment e, product p, serviceordertype st, serviceorderstatus sos, location l "
				+ "where so.serviceordertypeid = st.servicetypeid and "
				+ "so.equipmentid = e.equipmentid and "
				+ "so.sostatusid = sos.sostatusid and "
				+ "e.productid = p.productid and "
				+ "e.locationid = l.locationid and  "
				+ "l.customerid = ? and sos.soStatusID <> 1 " + "order by 1";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, customerid);
		rs = ps.executeQuery();
		return rs;
	}

	public ResultSet customerList() throws ClassNotFoundException,
			SQLException, InstantiationException, IllegalAccessException {

		myDB.createConnection();
		String myQuery = "SELECT Customer.customerid as customerid, Customer.customername as customername, CustomerType.customertypedesc as customertype,  Customer.BillingCity||', '||Customer.BillingState as local, Customer.contactfirstname||' '||Customer.contactlastname as contactname, Customer.contactphone as contactphone "
				+ " FROM Customer, CustomerType WHERE Customer.customertypeid = CustomerType.customertypeid "
				+ " ORDER BY Customer.customername";

		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}

	public void setCustomer(int customerid) throws ClassNotFoundException,
			SQLException, InstantiationException, IllegalAccessException {

		myDB.createConnection();
		String myQuery = "SELECT Customer.customerid as customerid, Customer.customername as customername, CustomerType.customertypedesc as customertype,  Customer.BillingCity as billingcity, Customer.BillingState as billingstate, "
				+ " Customer.BillingStreet as billingstreet, Customer.contactfirstname as contactfirstname, Customer.contactlastname, Customer.contactphone as contactphone, Customer.contactemail as contactemail, Customer.billingzip as billingzip, Customer.contactposition "
				+ " FROM Customer, CustomerType WHERE Customer.customertypeid = CustomerType.customertypeid "
				+ " AND Customer.customerid =?";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, customerid);
		rs = ps.executeQuery();
		while (rs.next()) {
			this.customerid = rs.getInt("customerid");
			customername = rs.getString("customername");
			customertypedesc = rs.getString("customertype");
			billingstreet = rs.getString("billingstreet");
			billingcity = rs.getString("billingcity");
			billingstate = rs.getString("billingstate");
			billingzip = rs.getString("billingzip");
			firstname = rs.getString("contactfirstname");
			lastname = rs.getString("contactlastname");
			phone = rs.getString("contactphone");
			email = rs.getString("contactemail");
			position = rs.getString("contactposition");
		}
	}

	public ResultSet getOverDuePM(int customerid)
			throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {

		myDB.createConnection();
		// sysdate = 2/1/2009

		String myQuery = "select e.equipmentid, p.productname, pm.pmdescription, ppm.frequency, to_char(max(so.closeddate), 'MM/DD/YYYY') "
				+ " as LastService, to_char((max(so.closeddate) + ppm.frequency), 'MM/DD/YYYY') as ServiceDue,  "
				+ " round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))) as DaysOverDue  "
				+ " from serviceorder so, equipment e, product p, productpm ppm, preventivemaintenance pm where so.equipmentid = e.equipmentid  "
				+ " and e.productid = p.productid and p.productid = ppm.productid and ppm.pmid = pm.pmid and so.pmid = pm.pmid and  "
				+ " e.statusid <>2 and so.equipmentid in  "
				+ "	(select e.equipmentid from equipment e, location l where e.locationid = l.locationid and l.customerid =?) group by e.equipmentid, p.productname,  "
				+ "	   pm.pmdescription, pm.pmid, ppm.frequency having  "
				+ "	      (round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency)))) > 0 and  "
				+ "	      ((max(so.closeddate)) > (max(so.opendate))) order by 7 desc, 1";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, customerid);
		rs = ps.executeQuery();
		return rs;
	}

	public ResultSet getDueSoon(int customerid) throws ClassNotFoundException,
			SQLException, InstantiationException, IllegalAccessException {

		myDB.createConnection();
		// sysdate = 2/1/2009

		String myQuery = "select e.equipmentid, p.productname, pm.pmdescription, ppm.frequency, to_char(max(so.closeddate), 'MM/DD/YYYY') as LastService, "
				+ " to_char((max(so.closeddate) + ppm.frequency), 'MM/DD/YYYY') as ServiceDue,  "
				+ " round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))*-1) as DaysUntilDue  "
				+ " from serviceorder so, equipment e, product p, productpm ppm, preventivemaintenance pm  "
				+ " where so.equipmentid = e.equipmentid and e.productid = p.productid  "
				+ " and p.productid = ppm.productid and ppm.pmid = pm.pmid and so.pmid = pm.pmid and e.statusid <>2 and  "
				+ " so.equipmentid in  "
				+ "	(select e.equipmentid from equipment e, location l where e.locationid = l.locationid and  "
				+ "      	l.customerid =?) group by e.equipmentid, p.productname, pm.pmdescription, pm.pmid, ppm.frequency having  "
				+ " 	(round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency)))) < 0 and  "
				+ " 	round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))*-1) < 30  "
				+ "	and ((max(so.closeddate)) > (max(so.opendate))) order by 7, 1";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, customerid);
		rs = ps.executeQuery();
		return rs;
	}

	public int getCustomerid() {
		return customerid;
	}

	public void setCustomerid(int userid) {
		this.customerid = userid;

	}

	public String getCustomerTypeDesc() {
		if (customertypedesc == null)
			customertypedesc = "";
		return customertypedesc;
	}

	public void setCustomerTypeDesc(String customertypedesc) {
		this.customertypedesc = customertypedesc;
	}

	public String getCustomerName() {
		if (customername == null)
			customername = "";
		return customername;
	}

	public void setCustomername(String customername) {
		this.customername = customername;
	}

	public String getBillingstreet() {
		if (billingstreet == null)
			billingstreet = "";
		return billingstreet;
	}

	public void setBillingstreet(String billingstreet) {
		this.billingstreet = billingstreet;
	}

	public String getBillingcity() {
		if (billingcity == null)
			billingcity = "";
		return billingcity;
	}

	public void setBillingcity(String billingcity) {
		this.billingcity = billingcity;
	}

	public String getBillingstate() {
		if (billingstate == null)
			billingstate = "";
		return billingstate;
	}

	public void setBillingstate(String billingstate) {
		this.billingstate = billingstate;
	}

	public String getBillingzip() {
		if (billingzip == null)
			billingzip = "";
		return billingzip;
	}

	public void setBillingzip(String billingzip) {
		this.billingzip = billingzip;
	}

	public String getFirstname() {
		if (firstname == null)
			firstname = "";
		return firstname;
	}

	public void setFirstname(String firstname) {
		this.firstname = firstname;
	}

	public String getLastname() {
		if (lastname == null)
			lastname = "";
		return lastname;
	}

	public void setLastname(String lastname) {
		this.lastname = lastname;
	}

	public String getPhone() {
		if (phone == null)
			phone = "";
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getEmail() {
		if (email == null)
			email = "";
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getPosition() {
		if (position == null)
			position = "";
		return position;
	}

	public void setPosition(String position) {
		this.position = position;
	}

	public void closeConnection() throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {
		myDB.closeConnection();
	}

}
